REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW — Replace the contents of a materialized view

Synopsis

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name

[ WITH [ NO ] DATA ]

Description

REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. You must be the owner of the materialized view to execute this command. The old contents are discarded. If WITH DATA is specified (or as the default), the backing query will be executed to provide new data, and the materialized view will be left in a scannable state. If WITH NO DATA is specified, no new data will be generated and the materialized view will be left in an unscannable state.

CONCURRENTLY and WITH NO DATA cannot be specified together.

Parameters

CONCURRENTLY

Refresh the materialized view without blocking concurrent reads on the materialized view. Without this option, a refresh that affects many rows will use fewer resources and complete faster, but may block other connections attempting to read from the materialized view. This option may be faster when only a small number of rows are affected.

This option is only allowed when there is at least one UNIQUE index on the materialized view that uses only column names and includes all rows. That is, it cannot be an expression index or include a WHERE clause.

This option cannot be used when the materialized view has not been populated yet.

Even with this option, only one REFRESH can run at a time for any given materialized view.

name

The name of the materialized view to refresh (optionally schema-qualified).

Notes

Although the default index for future CLUSTER operations is maintained, REFRESH MATERIALIZED VIEW does not order the produced rows based on this property. If you want the data sorted when it is produced, you must use an ORDER BY clause in the backing query.

Examples

-- This command will replace the contents of the materialized view order_summary using the query from its definition, and leave it in a scannable state:

REFRESH MATERIALIZED VIEW order_summary;

-- This command will release the storage associated with the materialized view annual_statistics_basis and leave it in an unscannable state:

REFRESH MATERIALIZED VIEW annual_statistics_basis WITH NO DATA;

See Also

CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, DROP MATERIALIZED VIEW